#!/usr/bin/python
# -*- coding: UTF-8 -*-
# 文件名：14-mysql操作类.py

'''通过导入时间模块，休眠5秒，可以看到是否自动关闭连接
import time
time.sleep(5)
db.close()
'''
import MySQLdb
import time
import re


class MysqldbHelper(object):
    """操作mysql数据库，基本方法

    	"""

    def __init__(self, host="127.0.0.1", username="jygsuser", password="Aa12345678", port=3306, database="ops", charset="utf8"):
        self.host = host
        self.username = username
        self.password = password
        self.database = database
        self.port = port
        self.con = None
        self.cur = None
        try:
            self.con = MySQLdb.connect(host=self.host, user=self.username, passwd=self.password, port=self.port, db=self.database, charset="utf8")
            # 所有的查询，都在连接 con 的一个模块 cursor 上面运行的
            self.cur = self.con.cursor(cursorclass=MySQLdb.cursors.DictCursor)
            self.cur.execute('SET NAMES UTF8')
        except:
            raise "DataBase connect error,please check the db config."

    def close(self):
        """关闭数据库连接

        """
        if not self.con:
            self.con.close()
            print "关闭数据库连接"
        else:
            raise "DataBase doesn't connect,close connectiong error;please check the db config."

    def getVersion(self):
        """获取数据库的版本号

        """
        self.cur.execute("SELECT VERSION()")
        time.sleep(5)
        return self.getOneData()

    def getOneData(self):
        # 取得上个查询的结果，是单个结果
        data = self.cur.fetchone()
        #{'VERSION()': u'5.6.40-log'}
        return data

    def creatTable(self, tablename, attrdict, constraint):
        """创建数据库表

        	args：
        		tablename  ：表名字
        		attrdict   ：属性键值对,{'book_name':'varchar(200) NOT NULL'...}
        		constraint ：主外键约束,PRIMARY KEY(`id`)
        """
        if self.isExistTable(tablename):
            print "{0} 存在 ".format(tablename)
            return
        sql = ''
        sql_mid = '`id` bigint(11) NOT NULL AUTO_INCREMENT,'
        for attr, value in attrdict.items():
            sql_mid = sql_mid + '`' + attr + '`' + ' ' + value + ','
        sql = sql + 'CREATE TABLE IF NOT EXISTS {0} ('.format(tablename)
        sql = sql + sql_mid
        sql = sql + constraint
        sql = sql + ') ENGINE=InnoDB DEFAULT CHARSET=utf8'
        # print 'creatTable:' + sql
        self.executeCommit(sql)

    def isExistTable(self, tablename):
        """判断数据表是否存在

        	args：
        		tablename  ：表名字

        	Return:
        		存在返回True，不存在返回False
        """
        sql = "select * from %s" % tablename
        result = self.executeCommit(sql)
        if result is None:
            print "{0} 存在 ".format(tablename)
            return True
        else:
            if re.search("doesn't exist", result):
                print "{0} 不存在 ".format(tablename)
                return False
            else:
                print "{0} 存在 ，不清楚何种场合会报出这个信息".format(tablename)
                return True

    def insert(self, tablename, params):
        """创建数据库表

        	args：
                    tablename  ：表名字
                    key        ：属性键
                    value      ：属性值
        """
        key = []
        value = []
        for tmpkey, tmpvalue in params.items():
            key.append(tmpkey)

            if isinstance(tmpvalue, str):
                value.append("'" + tmpvalue + "'")
            else:
                value.append(str(tmpvalue))

        attrs_sql = '(' + ','.join(key) + ')'
        values_sql = ' values(' + ','.join(value) + ')'

        sql = 'insert into %s' % tablename
        sql = sql + attrs_sql + values_sql

        print '===插入的SQL语句： ' + sql + '==='
        self.executeCommit(sql)


    def insertMany(self, table, attrs, values):
        """插入多条数据

        	args：
        		tablename  ：表名字
        		attrs		 ：属性键
        		values		：属性值

        	example：
        		table='test_mysqldb'
        		key = ["id" ,"name", "age"]
        		value = [(101, "liuqiao", "25"), (102,"liuqiao1", "26"), (103 ,"liuqiao2", "27"), (104 ,"liuqiao3", "28")]
        		mydb.insertMany(table, key, value)
        """
        attrs_sql = '(' + ','.join(attrs) + ')'
        print attrs_sql

        values_sql = ' values '
        # 遍历列表，取出每一项，也是列表
        for v in values:
            # 拼接成字符串
            values_sql = values_sql + str(v) + ","
        print values_sql[:-1]

        #  正确的 insert into test_mysqldb(age,sex,name,income)
        # values('102','m','caixinglong3',500),('202','m','caixinglong3',500);

        sql = 'insert into {0}'.format(table)
        sql = sql + attrs_sql + values_sql[:-1]
        self.executeCommit(sql)
        print '===插入的SQL语句： ' + sql + '==='
    def select(self, tablename, cond_dict='', order='', fields='*'):
        """查询数据

        	args：
        		tablename  ：表名字
        		cond_dict  ：查询条件
        		order	   ：排序条件

        	example：
        		print mydb.select(table)
        		print mydb.select(table, fields=["name"])
        		print mydb.select(table, fields=["name", "age"])
        		print mydb.select(table, cond_dict ={"id": '=1', "income": '<4000'})
        """
        consql = ' '
        if cond_dict != '':
            for k, v in cond_dict.items():
                consql = consql + k + v + ' and '
        consql = consql + ' 1=1 '
        ordersql = 'order by '
        if order != '':
            for k, v in order.items():
                ordersql = ordersql + k + ' ' + v + ' , '
        ordersql = ordersql[-1:0]

        if fields == "*":
            sql = 'select * from {0} where '.format(tablename)
        else:
            if isinstance(fields, list):
                fields = ",".join(fields)
                sql = 'select {0} from {1} where '.format(fields, tablename)
            else:
                raise "fields input error, please input list fields."

        sql = sql + consql + ordersql
        print 'select:' + sql
        return self.executeSql(sql)

    def delete(self, tablename, cond_dict):
        """删除数据

        	args：
        		tablename  ：表名字
        		cond_dict  ：删除条件字典

        	example：
        		params = {"name" : "caixinglong", "age" : "38"}
        		mydb.delete(table, params)

        """
        consql = ' '
        if cond_dict!='':
            for k, v in cond_dict.items():
                if isinstance(v, str):
                    v = "\'" + v + "\'"
                consql = consql + tablename + "." + k + '=' + v + ' and '
        consql = consql + ' 1=1 '
        sql = "DELETE FROM %s where%s" % (tablename, consql)
        print sql
        return self.executeCommit(sql)
    
    def update(self, tablename, attrs_dict, cond_dict):
        """更新数据

        	args：
        		tablename  ：表名字
        		attrs_dict	：更新属性键值对字典
        		cond_dict  ：更新条件字典

        	example：
        		params = {"name" : "caixinglong", "age" : "38"}
        		cond_dict = {"name" : "liuqiao", "age" : "18"}
        		mydb.update(table, params, cond_dict)

        """
        attrs_list = []
        consql = ' '
        for tmpkey, tmpvalue in attrs_dict.items():
            attrs_list.append("`" + tmpkey + "`" + "=" +"\'" + tmpvalue + "\'")
        attrs_sql = ",".join(attrs_list)
        print "attrs_sql:", attrs_sql
        if cond_dict!='':
            for k, v in cond_dict.items():
                if isinstance(v, str):
                    v = "\'" + v + "\'"
                consql = consql + "`" + tablename +"`." + "`" + k + "`" + '=' + v + ' and '
        consql = consql + ' 1=1 '
        sql = "UPDATE %s SET %s where%s" % (tablename, attrs_sql, consql)
        print sql
        return self.executeCommit(sql)
    
    def executeSql(self, sql=''):
        """执行sql语句，针对读操作返回结果集

        	args：
        		sql	 ：sql语句
        """
        try:
            self.cur.execute(sql)
            records = self.cur.fetchall()
            return records
        except MySQLdb.Error, e:
            error = 'MySQL execute failed! ERROR ({0}): {1}'.format(e.args[0], e.args[1])
            print error

    def executeCommit(self, sql=''):
        """执行数据库sql语句，针对更新,删除,事务等操作失败时回滚

        """
        try:
            print "开始执行sql语句"
            self.cur.execute(sql)
            self.con.commit()
            print " ===执行=== \n {0} \n === OK ===".format(sql)
        except MySQLdb.Error, e:
            self.con.rollback()
            error = 'MySQL execute failed! ERROR {0}: {1}'.format(e.args[0], e.args[1])
            # print "error:", error
            return error


if __name__ == "__main__":
    mydb = MysqldbHelper()
    # print mydb.getVersion()

    table = 'test_mysqldb'
    # attrs = {'name': 'varchar(200) DEFAULT NULL', 'age': 'int(11) DEFAULT NULL', 'income': 'int(11)', 'sex': "enum('m','w') DEFAULT 'm'"}
    # constraint = 'PRIMARY KEY(`id`)'

    # mydb.creatTable(table, attrs, constraint)

    # params = {"name": "caixinglong2", "age": "11", "income": 3300, "sex": 'm', }
    # mydb.insert('test_mysqldb', params)
    # 相当于执行类型的insert into test_mysqldb(age,sex,name,income) values('32','m','caixinglong2',100)

    # print mydb.select(table)
    # 得到({'age': 11L, 'income': 3300L, 'id': 1L, 'name': u'caixinglong2', 'sex': u'm'},)
    # print mydb.select(table, fields=["name", "age"])

    # print mydb.select(table, cond_dict={"id": '=1', "income": '<4000'})
    # print mydb.select(table, fields=["age", "name"], cond_dict={"id": '=1', "income": '<4000'})
    # print mydb.select(table, order={"income": 'desc', "age": 'asc'})

# select * from test_mysqldb  order by income desc , age asc;

    # attrs = ["income", "name", "age"]
    # values = [('3101', "liuqiao", "25"), ('3102', "liuqiao1", "26"), ('3104', "liuqiao2", "27"), ('3104', "liuqiao3", "28")]
    # mydb.insertMany(table, attrs, values)
    params = {"name": "caixinglong", "age": "8", }
    
    # mydb.delete(table, params)
    cond_dict = {"name": "liuqiao33", "age": "288"}

    # 仅更新一条数据，前边参数是修改后的值，后面是查询条件
    mydb.update(table, params, cond_dict)